\documentclass[12pt]{beamer}
%\usepackage{beamerthemesplit}

\usepackage{fontspec}
\setsansfont{PT Sans}
\setmonofont{PT Mono}

\usepackage{beamerthemesplit}
\usefonttheme[onlymath]{serif}

\usetheme{CambridgeUS} 
\usecolortheme{lily} 

\hypersetup{unicode=true}
\DeclareFontEncoding{T1}
\fontencoding{T1}
\usepackage[russian]{babel}

\usepackage{amsmath,amssymb,amsthm}
\usepackage{graphics,graphicx}
\usepackage{listings}



%\usepackage{media9}
\usepackage{fancyvrb} 
\usepackage{booktabs} 
\usepackage{colortbl}
\usepackage{multirow}
\usepackage{verbatim}
%\usepackage{fancyvrb}
\usepackage{color} 
\usepackage{url}
\usepackage{qrcode}
\usepackage{pifont}% http://ctan.org/pkg/pifont


\definecolor{mygreen}{RGB}{28,172,0} % color values Red, Green, Blue
\definecolor{mylilas}{RGB}{170,55,241}
\definecolor{codecolor}{RGB}{0,120,0}
\definecolor{light-gray}{gray}{0.95}
\definecolor{light-green}{RGB}{205,255,204}
\definecolor{light-red}{rgb}{0.9, 0.8, 0.8}
\definecolor{light-yellow}{rgb}{1,1,0.85}
\definecolor{light-blue}{RGB}{255,204,153}
\definecolor{dark-blue}{rgb}{0,0,0.6}
\definecolor{dark-red}{RGB}{204,0,0}
\definecolor{dark-green}{RGB}{28,172,0}

\newcommand{\cmark}{\textcolor{green}{\ding{51}}}
\newcommand{\xmark}{\textcolor{red}{\ding{55}}}

\setbeamercolor{frametitle}{fg=dark-red,bg=gray!10}
\setbeamerfont{title}{series=\bfseries,parent=structure}
\setbeamerfont{frametitle}{series=\bfseries,parent=structure}
\setbeamertemplate{blocks}[rounded][shadow=false]
\setbeamercolor{block title}{fg=structure,bg=white}
\setbeamertemplate{navigation symbols}{}

\renewcommand{\emph}[1]{\textcolor{dark-blue}{#1}}
\newcommand{\emphb}[1]{\textcolor{dark-blue}{\textbf{#1}}}

\usepackage{tikz}
\usetikzlibrary{er,arrows,matrix,positioning,shapes,snakes}
\usetikzlibrary{calc,patterns,decorations.pathmorphing,decorations.markings,positioning}
\usetikzlibrary{arrows,matrix,positioning,decorations,snakes}
\colorlet{myRed}{red!20}
\tikzset{
  rows/.style 2 args={/utils/temp/.style={row ##1/.append style={nodes={#2}}},
    /utils/temp/.list={#1}},
  columns/.style 2 args={/utils/temp/.style={column ##1/.append style={nodes={#2}}},
    /utils/temp/.list={#1}}}
\makeatletter
\tikzset{anchor/.append code=\let\tikz@auto@anchor\relax,
  add font/.code=%
    \expandafter\def\expandafter\tikz@textfont\expandafter{\tikz@textfont#1},
  left delimiter/.style 2 args={append after command={\tikz@delimiter{south east}
    {south west}{every delimiter,every left delimiter,#2}{south}{north}{#1}{.}{\pgf@y}}}}
\makeatother

%\warn{\matcode}[1]{ \textcolor{codecolor}{\texttt{#1}}}
%\newcommand{\matcode}[1]{\begin{Verbatim}[formatcom=\color{red}]#1\end{Verbatim}}
\definecolor{codecolor}{RGB}{0,150,0}
\newcommand{\matcode}[1]{ \textcolor{codecolor}{\texttt{#1}}}


\title{Изменение данных, Представления Процедуры и Триггеры}
\subtitle{Базы данных}
\author[Кафедра ММЭ]{Юдинцев В. В.}
\institute[Самарский университет]{Кафедра математических методов в экономике}

%\pgfdeclareimage[height=1cm]{logo}{ssau.pdf}
%\logo{\pgfuseimage{logo}}
%\setbeameroption{show notes}
%\graphicspath{{img/}}

\usepackage{minted}
\usepackage{mdframed}

\surroundwithmdframed{minted}
\mdfsetup{innerleftmargin=0.1cm,innerrightmargin=0.1cm,linecolor=light-gray,linewidth=0,skipabove=1mm,skipbelow=1cm,innerbottommargin=2mm}

\setminted[SQL]{fontsize=\scriptsize,encoding=utf8,breaklines=true}

% frame=lines,framesep=2\fboxsep
\makeatletter
\AtBeginEnvironment{minted}{\dontdofcolorbox}
\def\dontdofcolorbox{\renewcommand\fcolorbox[4][]{##4}}
\makeatother

\lstset{language=SQL,          
    keepspaces=true,
    extendedchars=true,
    basicstyle={\footnotesize  \color{black}},
    breaklines=true,    
    keywordstyle=\color{blue},
    morekeywords=[2]{1}, keywordstyle=[2]{\color{codecolor}},
    identifierstyle={ \bf \color{black} },
    stringstyle=\color{mylilas},
    commentstyle=\color{mygreen},%
    showstringspaces=false,
    numbers=left,
    numberstyle={\tiny \color{gray}},
    numbersep=4pt,   
    frame=single 
}

\makeatother
\setbeamertemplate{footline}
{
  \leavevmode%
  \hbox{%  
  \begin{beamercolorbox}[wd=.3\paperwidth,ht=2.25ex,dp=1ex,center]{author in head/foot}%
    \usebeamerfont{author in head/foot}\insertshortauthor
  \end{beamercolorbox}%  
  \begin{beamercolorbox}[wd=.6\paperwidth,ht=2.25ex,dp=1ex,center]{title in head/foot}%
    \usebeamerfont{title in head/foot}\insertshorttitle
  \end{beamercolorbox}%
  \begin{beamercolorbox}[wd=.1\paperwidth,ht=2.25ex,dp=1ex,center]{date in head/foot}%
  \insertframenumber{} / \inserttotalframenumber\hspace*{1ex}
% \insertframenumber{} 
  \end{beamercolorbox}}%
  \vskip0pt%
}

\setbeamertemplate{headline}{}
%\setbeamertemplate{footline}{1}

\AtBeginSection[]{ 
  \begin{frame}[plain]
  %\vfill  
  \begin{tikzpicture}
  \useasboundingbox (0,0) rectangle(\the\paperwidth,\the\paperheight);  
  \fill[color=dark-red]   (-1cm, 3.9cm) rectangle(\the\paperwidth, 5.9cm);
 %\usebeamerfont{title}\insertsectionhead\par%
  %\node[text width=\the\paperwidth,align=center] at (current page.center) {\color{ExecusharesWhite}\Large\textbf{\insertsectionhead}};  
\node[text width=\the\paperwidth,align=center] at (6cm,4.9cm) {\color{white}\Large\textbf{\insertsectionhead}};
  \end{tikzpicture}
  \end{frame}
}

% 
% Документ
%
%
\begin{document}

{
\setbeamercolor{background canvas}{bg=white} 
\usebackgroundtemplate{\includegraphics[width=\paperwidth,height=\paperheight]{back.png}}
\begin{frame}[plain]
\maketitle
\end{frame}
}

\frame{
\frametitle{Содержание}
\tableofcontents
}

\section{INSERT}

\begin{frame}[fragile]
\frametitle{Шаблон оператора INSERT}

\begin{itemize}
  \item Оператор \emphb{INSERT} предназначен для добавления строк в таблицу
  \item Вставка одной строки:
\begin{minted}{SQL}   
INSERT INTO ИМЯ_ТАБЛИЦЫ 
  (ИМЯ_СТОЛБЦА_1, ИМЯ_СТОЛБЦА_2, ИМЯ_СТОЛБЦА_3, ...)
VALUES 
  (ЗНАЧЕНИЕ_1, ЗНАЧЕНИЕ_2, ЗНАЧЕНИЕ_3, ...);
\end{minted}
\item Вставка нескольких новых строк:
\begin{minted}{SQL}  
INSERT INTO ИМЯ_ТАБЛИЦЫ 
  (ИМЯ_СТОЛБЦА_1, ИМЯ_СТОЛБЦА_2, ИМЯ_СТОЛБЦА_3, ...)
VALUES 
  (ЗНАЧЕНИЕ_11, ЗНАЧЕНИЕ_12, ЗНАЧЕНИЕ_13, ...),
  (ЗНАЧЕНИЕ_21, ЗНАЧЕНИЕ_22, ЗНАЧЕНИЕ_23, ...),
  (ЗНАЧЕНИЕ_31, ЗНАЧЕНИЕ_32, ЗНАЧЕНИЕ_33, ...);
\end{minted}
\end{itemize}
\end{frame} 

\begin{frame}[fragile]
\frametitle{Пример}
Таблица \emphb{students} состоит из трех столбцов: идентификатор (\emphb{id}), имя (\emphb{name}), номер зачетной книжки (\emphb{grade\_book}):
\begin{minted}{SQL}  
CREATE TABLE students (
  id BIGINT UNSIGNED PRIMARY KEY NOT NULL, 
  name VARCHAR(100), 
  grade_book CHAR(10)
);
\end{minted}  
Вставка новой строки в таблицу \emphb{students}:
\begin{minted}{SQL}  
INSERT INTO students 
  (id, name, grade_book) 
VALUES 
  (1, 'Раскольников Р. Р.', '1860-00044');
\end{minted}    
\end{frame} 

\begin{frame}[fragile]
\frametitle{AUTO\_INCREMENT}
Если в таблице содержится поле с атрибутом \emphb{AUTO\_INCREMENT}, то значение этого поля можно не указывать. 
\begin{minted}{SQL}  
CREATE TABLE students (
  id BIGINT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, 
  name VARCHAR(100), 
  grade_book CHAR(10)
);
\end{minted}    
Вставка нескольких строк в таблицу \emphb{students}:
\begin{minted}{SQL}  
INSERT INTO students (name, grade_book) 
VALUES 
  ('Раскольников Р. Р.', '1860-00044'),
  ('Разумихин Д. С.', '1860-00046');
\end{minted} 
Таблица \emphb{students}
\begin{minted}{SQL}  
id|name              |grade_book|
--|------------------|----------|
 1|Раскольников Р. Р.|1860-00044|
 2|Разумихин Д. С.   |1860-00046|  
\end{minted}  
\end{frame}  

\begin{frame}[fragile]
\frametitle{INSERT INTO ... SELECT}
Новые строки могут быть вставлены из другой таблицы. В этом случае \emphb{VALUES} заменяется на \emphb{SELECT}. Например, для вставки информации о новых студентах из таблицы абитуриентов может использоваться следующий SQL-сценарий: 
\begin{minted}{SQL}  
INSERT INTO students (name)
  SELECT 
    name
  FROM
    enrollees;
\end{minted}    
\end{frame}  

\section{UPDATE}
\begin{frame}[fragile]
\frametitle{Шаблон оператора UPDATE}
\begin{itemize}
  \item Оператор \emphb{UPDATE} используется для обновления данных в таблице
  \item Шаблон
\begin{minted}{SQL} 
UPDATE 
  ИМЯ_ТАБЛИЦЫ 
SET 
  СТОЛБЕЦ_1 = ЗНАЧЕНИЕ, СТОЛБЕЦ_2 = ЗНАЧЕНИЕ
WHERE 
  УСЛОВИЕ
\end{minted}  
\end{itemize}
\end{frame}  

\begin{frame}[fragile]
\frametitle{Пример}
Изменение номера зачетной книжки с \emph{1860-00046} на \emph{1860-00010}:
\begin{minted}{SQL} 
UPDATE 
  students
SET 
  grade_book = '1860-00010'
WHERE 
  STRCMP(grade_book,'1860-00046') = 0;
\end{minted}  
\end{frame}  

\begin{frame}[fragile]
\frametitle{Функция RELPACE}
Использование функции \emphb{REPLACE} для замены части строки столбца:
\begin{minted}{SQL} 
REPLACE(СТРОКА, ЧТО_НАЙТИ, НА_ЧТО_ЗАМЕНИТЬ)
\end{minted}  
Исправить номера зачетных книжек с \emph{1860-*****} на \emph{1861-*****}:
\begin{minted}{SQL} 
UPDATE 
  students
SET 
  grade_book = REPLACE(grade_book,'1860','1861')
WHERE   
  grade_book LIKE '%1860%';
\end{minted}  
\end{frame}  

\section{DELETE}

\begin{frame}[fragile]
\frametitle{Шаблон оператора DELETE}
\begin{itemize}
  \item Оператор \emphb{DELETE} предназначен для удаления строк из таблицы
  \item Шаблон
\begin{minted}{SQL}
DELETE FROM 
  ИМЯ_ТАБЛИЦЫ
WHERE 
  УСЛОВИЯ
\end{minted}  
\end{itemize}
\end{frame}  

\begin{frame}[fragile]
\frametitle{Пример}
Удаление записей с заданным значением поля \emphb{id}: 
\begin{minted}{SQL} 
  DELETE FROM 
    students
  WHERE 
    id = 1;
\end{minted}  
Удаление записей с заданным значением строкового поля, использую функцию \emphb{STRCMP}:
\begin{minted}{SQL} 
  DELETE FROM 
    students
  WHERE 
    STRCMP(grade_boook,'1860-00001') = 0;
\end{minted}  
\end{frame}

\section{VIEW}

\begin{frame}[fragile]
\frametitle{Представление это...}
\begin{itemize}
\item Представление \emphb{VIEW} это поименованный запрос (\emphb{SELECT}) 
\item Представление можно рассматривать как виртуальную таблицу, которая формируются из данных других таблиц при обращении к представлению
\item Шаблон
\begin{minted}{SQL}  
CREATE VIEW 
  ИМЯ_ПРЕДСТАВЛЕНИЯ 
AS 
  ВЫРАЖЕНИЕ_с_SELECT
\end{minted}
\end{itemize}
\end{frame} 

\begin{frame}[fragile]
\frametitle{Пример базы данных}
\includegraphics[width=0.8\linewidth]{teachers3.png}
\end{frame}  

\begin{frame}[fragile]
\frametitle{Запрос к двум таблицам}
Запрос, возвращающий список преподавателей с наименованиями должностей
\begin{minted}{SQL}  
SELECT 
  name as teacher_name, 
  position.name as position_name  
FROM
  teacher
LEFT JOIN
  teacher_position ON teacher_position.id = teacher.position_id
\end{minted}
Результат выполнения запроса
\begin{minted}{SQL}  
teacher_name   |position_name|
---------------|-------------|
Громов В. И.   |Профессор    |
Петров М. П.   |Профессор    |
Скрыль М. Э.   |Доцент       |
Селезнёв И. А. |Профессор    |
Метелкина П. И.|             |
\end{minted}  
\end{frame} 

\begin{frame}[fragile]
\frametitle{Представление на основе запроса}
Создание представления \emphb{teacher\_position\_name}:
\begin{minted}{SQL}  
CREATE VIEW 
  teacher_position_name (teacher_name, position_name)
AS SELECT 
     teacher.name, teacher_position.name
   FROM 
     teacher
   LEFT JOIN 
     teacher_position 
   ON teacher_position.id = teacher.position_id;
\end{minted}
\end{frame} 

\begin{frame}[fragile]
\frametitle{Представление как виртуальная таблица}
Для оператора \emphb{SELECT} Представление -- это обычная таблица
\begin{minted}{SQL}  
SELECT * FROM teacher_position;
\end{minted}
Результат: 
\begin{minted}{SQL}  
teacher_name   |position_name|
---------------|-------------|
Громов В. И.   |Профессор    |
Петров М. П.   |Профессор    |
Скрыль М. Э.   |Доцент       |
Селезнёв И. А. |Профессор    |
Метелкина П. И.|             |
\end{minted}
Одно из назначений \emph{Представления} -- возможность скрытия часто используемых сложных запросов, собирающих данные из нескольких таблиц.  
\end{frame} 

\begin{frame}[fragile]
\frametitle{Пример}
Создание представления \emphb{exam\_progress}, формирующее список экзаменов с количеством студентов, которые сдали каждый экзамен:
\begin{minted}{SQL}  
CREATE VIEW 
  exam_progress
AS SELECT 
     exam.title as exam_title, 
     COUNT(exam_result.student_id) as count_student
   FROM 
     exam_result
   LEFT JOIN exam 
     ON exam.id = exam_result.exam_id
   GROUP BY
     exam.title 
   ORDER BY
     count_student DESC;
\end{minted}
\end{frame} 

\begin{frame}[fragile]
\frametitle{Пример}
Использование представления
\begin{minted}{SQL}  
SELECT 
  * 
FROM
  exam_progress 
ORDER BY
  count_student ASC;
\end{minted}
Результат 
\begin{minted}{SQL}  
exam_title  |count_student|
------------|-------------|
Эконометрика|            1|
Базы данных |            2|
Информатика |            2|
\end{minted}
\end{frame} 

\begin{frame}[fragile]
\frametitle{Удаление Представления}
\begin{itemize}
\item Удалить представление \emphb{exam\_progress} (должно существовать)
\begin{minted}{SQL}  
DROP VIEW exam_progress;
\end{minted}
\item Удалить, если существует (если представления нет, то не возникнет сообщения об ошибке)  
\begin{minted}{SQL}  
DROP VIEW IF EXISTS exam_progress;
\end{minted}
\end{itemize}
\end{frame}

\begin{frame}[fragile]
\frametitle{Использование}
\begin{itemize}
  \item Скрытие сложности структуры базы данных, "сборка" данных после нормализации
  \item Повышение безопасности: пользователю открывается доступ к ограниченному набору данных из таблицы    
\end{itemize}
\end{frame} 

\section{Процедуры и функции}

\begin{frame}[fragile]
\frametitle{Процедура}
\begin{itemize}
  \item Процедура подобна подпрограмме в языках программирования общего назначения
  \item \emph{Процедуры}, как и \emph{Представления} -- это именованые наборы выражений языка SQL
  \item Процедура компилируется один раз и хранится на сервере  
  \item В отличие от \emph{Представления}, в \emph{Процедуру} можно передать параметры
  \item Процедура может принимать переменные, возвращать результаты или изменять переменные и возвращать их  
  \item \emph{Процедура} вызывается при помощи команды \emphb{CALL}  
\end{itemize}
\end{frame} 

\begin{frame}[fragile]
\frametitle{Функция}
\begin{itemize}
  \item \emph{Функция} -- это именованые блоки выражений языка SQL
  \item В отличие от \emph{Процедуры}, \emph{Функция} возвращает одно значение и может использоваться в SQL-выражениях также, как встроенные функции (STRCMP, FORMAT\_DATE, ...)  
\end{itemize}
\end{frame} 

\begin{frame}[fragile]
\frametitle{Шаблон}
\begin{minted}{SQL} 
DELIMITER $$  

CREATE PROCEDURE ИМЯ_ПРОЦЕДУРЫ(СПИСОК_ПАРАМЕТРОВ)
BEGIN
   ВЫРАЖЕНИЕ 1;
   ВЫРАЖЕНИЕ 2;
   ...
END $$ 

DELIMITER ;
\end{minted}
\end{frame}

\begin{frame}[fragile]
\frametitle{Разделитель}
\begin{itemize}
  \item Процедура -- соcтавное выражение, которое может включать из нескольких SQL-выражений, каждое из которых заканчивается точкой с запятой. 
  \item Для объявления процедуры, как составного выражения, необходимо определить символ окончания этого составного выражения, который должен отличаться от символа, которые разделяет SQL-выражения внутри самой процедуры. 
  \item Для переопределения разделителя используется директива \emph{DELIMITER}. 
\end{itemize}
\begin{minted}{SQL} 
DELIMITER $$
\end{minted}
Определен символ-разделитель \emphb{\$\$}, которым закончится определение \emph{Процедуры} или \emph{Функции}
\end{frame}


\begin{frame}[fragile]
\frametitle{Создание Процедуры}
Определяется временный символ-разделитель:
\begin{minted}{SQL} 
DELIMITER $$
\end{minted}
Объявление процедуры с именем \emphb{GetTeachers} без аргументов: 
\begin{minted}{SQL} 
CREATE PROCEDURE GetTeachers()  
BEGIN
  SELECT 
     teacher.name, teacher_position.name
  FROM 
     teacher
  LEFT JOIN teacher_position 
  ON teacher_position.id = teacher.position_id;
END $$
\end{minted}
Определение процедуры заканчивается символом \emphb{\$\$}, который обозначает окончание составного выражения -- определения процедуры.
После завершения определения процедуры символом-разделителем снова становится точка с запятой:
\begin{minted}{SQL} 
DELIMITER ;
\end{minted}
\end{frame}



\begin{frame}[fragile]
\frametitle{Вызов процедуры}
\begin{minted}{SQL} 
CALL GetTeachers();

teacher_name   |position_name|
---------------|-------------|
Громов В. И.   |Профессор    |
Петров М. П.   |Профессор    |
Скрыль М. Э.   |Доцент       |
Селезнёв И. А. |Профессор    |
Метелкина П. И.|             |
\end{minted}
\end{frame}

\begin{frame}[fragile]
\frametitle{Процедура с аргументами}
В скобках после имени процедуры перечисляются входные (\emphb{IN}) аргументы через запятую с указанием их типов:
\begin{minted}{SQL} 
DELIMITER $$  

CREATE PROCEDURE ИМЯ_ПРОЦЕДУРЫ(IN имя1 тип1, IN имя2 тип2)
BEGIN
   ВЫРАЖЕНИЕ 1;
   ВЫРАЖЕНИЕ 2;
   ...
END $$ 

DELIMITER ;
\end{minted}
\end{frame}

\begin{frame}[fragile]
\frametitle{Пример}
\begin{minted}{SQL} 
DELIMITER $$

CREATE PROCEDURE GetStudentBetterThan(IN mark FLOAT)  
BEGIN
  SELECT 
    student.name, AVG(exam_result.mark) as mean_mark
  FROM 
    exam_result
  LEFT JOIN student
    ON student.id = exam_result.student_id
  GROUP BY
    student.name
  HAVING mean_mark > mark;
END $$

DELIMITER ;
\end{minted}
\end{frame}

\begin{frame}[fragile]
\frametitle{Вызов процедуры с аргументами}
Список студентов со средней оценкой больше 4,5: 
\begin{minted}{SQL} 
CALL GetStudentBetterThan(4.5);  
\end{minted}
Результат
\begin{minted}{SQL} 
name     |mean_mark|
---------|---------|
Садовский|   5.0000|
Фетисова |   5.0000|
\end{minted}
\end{frame}

\begin{frame}[fragile]
\frametitle{Процедура с возвращаемым аргументом}
Процедура возвращает среднее значение оценок полученных на экзамене, наименование которого указанного первым аргументом
\begin{minted}{SQL} 
DELIMITER $$;
CREATE PROCEDURE 
GetMeanExamMark(IN exam_title VARCHAR(50), OUT mean_mark FLOAT)  
BEGIN
  SELECT 
    AVG(exam_result.mark)
  FROM  
    exam_result
  LEFT JOIN exam 
    ON exam.id = exam_result.exam_id 
  WHERE 
    STRCMP(exam.title,exam_title) = 0
  GROUP BY
    exam.id
  INTO mean_mark;
END $$
DELIMITER ;
\end{minted}
\end{frame}

\begin{frame}[fragile]
\frametitle{Пример}
Средняя оценка по экзамену \emph{Базы данных}:
\begin{minted}{SQL} 
CALL GetMeanExamMark('Базы данных',@res);
\end{minted}
Результат записывается в переменную \emphb{@res}. Значение переменной 
\begin{minted}{SQL} 
SELECT @res;
\end{minted}
\end{frame}


\begin{frame}[fragile]
\frametitle{Шаблон определения функции}
\begin{minted}{SQL} 
CREATE FUNCTION имя_функции(агрумент1 тип1, аргумент2 тип2)
  RETURNS тип_возвращаемого_значения
    BEGIN
    ...
    ...
    RETURN значение;
    END 
\end{minted}
\end{frame} 


\begin{frame}[fragile]
\frametitle{Пример. Объявление функции}
Перевод оценки из пятибальной шкалы в 100-бальную:
\begin{minted}{SQL}
DELIMITER $$

CREATE FUNCTION SCALE_MARK_100(mark FLOAT, max_mark FLOAT)
  RETURNS FLOAT
BEGIN
  DECLARE v1 FLOAT;
  SET v1 = 100*mark/max_mark;
  RETURN v1;
END $$

DELIMITER ; 
\end{minted}
\end{frame}

\begin{frame}[fragile]
\frametitle{Пример. Использование функции}
\begin{minted}{SQL}
SELECT 
  exam.title as exam_title, 
  student.name as student_name, 
  SCALE_MARK_100(exam_result.mark,5) as mark100
FROM 
  exam_result
LEFT JOIN exam 
  ON exam.id = exam_result.exam_id
LEFT JOIN student 
  ON exam_result.student_id = student.id
ORDER BY 
  exam_title;

exam_title  |student_name|mark100|
------------|------------|-------|
Базы данных |Садовский   |    100|
Базы данных |Никонова    |     80|
Информатика |Никонова    |     80|
Информатика |Фетисова    |    100|
Эконометрика|Садовский   |    100|  
\end{minted}  
\end{frame}


\begin{frame}[fragile]
\frametitle{Пример. Без использования функции}
Можно формировать математические выражения со значениями столбцов 
\begin{minted}{SQL}
SELECT 
  exam.title as exam_title, 
  student.name as student_name, 
  100*exam_result.mark/5 as mark100
FROM 
  exam_result
LEFT JOIN exam 
  ON exam.id = exam_result.exam_id
LEFT JOIN student 
  ON exam_result.student_id = student.id
ORDER BY 
  exam_title;
\end{minted}  
\end{frame}

\begin{frame}[fragile]
\frametitle{IF ... THEN ... ELSE}
Функция возвращает <<Зачёт>>, если оценка больше или равна заданной границы (второй аргумент) и <<Незачёт>>, если оценка меньше границы или отсутсвует:
\begin{minted}{SQL}
DELIMITER $$;

CREATE FUNCTION PassFail(mark FLOAT,margin FLOAT) RETURNS CHAR(7)
BEGIN
  DECLARE RES CHAR(7);
  IF (mark < margin OR mark is NULL) THEN
    RETURN 'Незачет';
  ELSE
    RETURN 'Зачет';
  END IF; 
END $$

DELIMITER ;
\end{minted} 
\end{frame}

\begin{frame}[fragile]
\frametitle{Использование функции PassFail}

\begin{minted}{SQL}
SELECT 
  student.name as student_name, 
  exam.title as exam_title, 
  PassFail(exam_result.mark,3.5) as PF
FROM 
  exam_result
LEFT JOIN exam 
  ON exam.id = exam_result.exam_id
RIGHT JOIN student 
  ON exam_result.student_id = student.id
ORDER BY 
  exam_title;

student_name|exam_title  |PF     |
------------|------------|-------|
Ишутин      |            |Незачет|
Садовский   |Базы данных |Зачет  |
Никонова    |Базы данных |Зачет  |
Фетисова    |Информатика |Зачет  |
Никонова    |Информатика |Зачет  |
Садовский   |Эконометрика|Зачет  |  
\end{minted} 
\end{frame}

\begin{frame}[fragile]
\frametitle{Альтернативный вариант}

\begin{minted}{SQL}
SELECT 
  student.name as student_name, 
  exam.title as exam_title, 
  IF(exam_result.mark>=3.5,'Зачёт','Незачёт') as PF
FROM 
  exam_result
LEFT JOIN exam 
  ON exam.id = exam_result.exam_id
RIGHT JOIN student 
  ON exam_result.student_id = student.id
ORDER BY 
  exam_title;

student_name|exam_title  |PF     |
------------|------------|-------|
Ишутин      |            |Незачет|
Садовский   |Базы данных |Зачет  |
Никонова    |Базы данных |Зачет  |
Фетисова    |Информатика |Зачет  |
Никонова    |Информатика |Зачет  |
Садовский   |Эконометрика|Зачет  |  
\end{minted} 
\end{frame}


\begin{frame}[fragile]
\frametitle{Удаление процедуры или функции}
\begin{minted}{SQL}
  DROP PROCEDURE имя_процедуры;
  
  DROP PROCEDURE IF EXISTS имя_процедуры;

  DROP FUNCTION имя_функции;

  DROP FUNCTION IF EXISTS имя_функции;
\end{minted}  
\end{frame}


\section{Триггеры}

\begin{frame}[fragile]
\frametitle{Триггеры это ...}
\begin{itemize}
  \item Хранимая процедура, которая исполняется при наступлении определенного \alert{события}
  \item Триггер связывается с таблицей, на \alert{события} в которой он реагирует
  \item Триггер может срабатывать при следующих \alert{изменениях} в таблице (тип триггера): 
  \begin{itemize}
    \item \texttt{BEFORE INSERT, AFTER INSERT}
    \item \texttt{BEFORE UPDATE, AFTER UPDATE}
    \item \texttt{BEFORE DELETE, AFTER DELETE}
  \end{itemize}
\end{itemize}
\end{frame} 

\begin{frame}[fragile]
\frametitle{Шаблон}
\begin{minted}{SQL}  
DELIMITER $$
CREATE TRIGGER имя_триггера тип_триггера ON таблица
  FOR EACH ROW
  BEGIN
    выражение1;
    выражение2;
  END; $$
DELIMITER ;
\end{minted}
\end{frame} 

\begin{frame}[fragile]
\frametitle{Пример}
При вставке новой записи в таблицу \emphb{students} необходимо генерировать автоматически номер зачетной книжки, который состоит из года поступления и идентификатора студента
\begin{minted}{SQL} 
CREATE TABLE students (
  id BIGINT UNSIGNED DEFAULT 0 PRIMARY KEY,
  name VARCHAR(50), 
  grade_book CHAR(10),
  enrollment_date DATE
);
\end{minted}
\end{frame} 


\begin{frame}[fragile]
\frametitle{Пример триггера BEFORE UPDATE}
Триггер срабатывает перед обновлением таблицы, и формирует значение для атрибута \emphb{grade\_book}:
\begin{minted}{SQL} 
DELIMITER $$

CREATE TRIGGER ON_UPDATE_STUDENT BEFORE UPDATE ON students
  FOR EACH ROW
  BEGIN
    SET NEW.grade_book = CONCAT(DATE_FORMAT(NEW.enrollment_date, "%Y-"),LPAD(NEW.id, 5, 0));
  END; $$
 
DELIMITER ;
\end{minted}
\end{frame} 

\begin{frame}[fragile]
\frametitle{Фукнция CONCAT}
Функция \emphb{CONCAT} склеивает две строки 
\begin{minted}{SQL} 
SELECT CONCAT('Строка1-','Строка2') AS res;

res            |
---------------|
Строка1-Строка2|
\end{minted}  
\end{frame}

\begin{frame}[fragile]
\frametitle{Функция FORMAT}
Функция \emphb{DATE\_FORMAT} возвращает тектовое представление даты в соотетсвии с заданным форматом:
\begin{minted}{SQL} 
DATE_FORMAT('2020-04-26','%Y')        => 2020
DATE_FORMAT('2020-04-26','%d.%m.%Y')  => 26.04.2020
DATE_FORMAT('2020-04-26','%d.%m.%y')  => 26.04.20
\end{minted}  
{\tiny \url{https://www.w3schools.com/sql/func_mysql_date_format.asp}}
\end{frame}

\begin{frame}[fragile]
\frametitle{Функция LPAD}
Функция \emphb{LPAD} дополняет заданную строку слева символами до заданной длины строки
\begin{minted}{SQL} 
SELECT LPAD('TEXT',10,'*');

LPAD('TEXT',10,'*')|
-------------------|
******TEXT         |
\end{minted}  

\end{frame}


\begin{frame}[fragile]
\frametitle{Пример триггера BEFORE INSERT}
\begin{minted}{SQL} 
DELIMITER $$

CREATE TRIGGER ON_NEW_STUDENT BEFORE INSERT ON students
  FOR EACH ROW
  BEGIN
    SELECT MAX(id) FROM students into @lastid;
    SET NEW.id = COALESCE(@lastid + 1, 1);
    IF NEW.enrollment_date IS NOT NULL THEN
      SET NEW.grade_book = CONCAT(DATE_FORMAT(NEW.enrollment_date, "%Y-"),LPAD(NEW.id, 5, 0));
    END IF;
  END; $$

DELIMITER ;
\end{minted}
До вставки записи (в триггере \emphb{BEFORE INSERT}) значение автоикрементного поля еще неизвестно, поэтому \emphb{id} студента объявлено \alert{не} как \emphb{AUTO\_INCREMENT} и новое значение \emphb{id} определяется внутри триггера
\end{frame} 

\begin{frame}[fragile]
\frametitle{Пример}
\begin{minted}{SQL} 
INSERT INTO students (name, enrollment_date) 
   values('Лопотухин В. Д','2016-10-20');

INSERT INTO students (name, enrollment_date) 
   values('Малахова  О. С','2016-10-21');
\end{minted}
Результат:
\begin{minted}{SQL} 
SELECT * FROM students;

id|name          |grade_book|enrollment_date|
--|--------------|----------|---------------|
 1|Лопотухин В. Д|2016-00001|     2016-10-20|
 2|Малахова  О. С|2016-00002|     2016-10-24|
\end{minted}
\end{frame} 

\begin{frame}[fragile]
\frametitle{Удаление триггера}
\begin{minted}{SQL} 
  DROP TRIGGER имя_триггера;
  DROP TRIGGER IF EXISTS имя_триггера;
\end{minted}
\end{frame}


\frame[plain]{
\frametitle{Список использованных источников}
\small
\begin{itemize}
  \item SQL Учебник \\
  {\scriptsize \url{https://schoolsw3.com/sql/index.php}}  
  \item SQL Tutorial \\
  {\scriptsize \url{https://www.tutorialspoint.com/sql/index.htm}}  
  \item MySQL 8.0 Reference Manual \\
  {\scriptsize \url{https://dev.mysql.com/doc/refman/8.0/en/introduction.html}}    
  \item Basic MySQL Tutorial \\
  {\scriptsize \url{https://www.mysqltutorial.org/basic-mysql-tutorial.aspx}}    
\end{itemize} 
}

\frame[plain,c]{
  \begin{center}
  \qrcode[height=1in]{https://classmech.ru/pages/databases/main}\\
  \vspace{2mm}
  {\tiny \url{https://classmech.ru/pages/databases/main} }
  \end{center}
}

\end{document}